﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace QLK.Database.DALs
{
    public class Invoices_ProductDAL
    {
        private ManageStoreDataContext db;

        public Invoices_ProductDAL()
        {
            db=new ManageStoreDataContext();
        }

        public List<Invoices_Product> Invoices_ProductGetALL()
        {
            var invoices_product = from i in db.Invoices_Products
                                   orderby i.Price
                                   select i;
            return invoices_product.ToList<Invoices_Product>();
        }

        public Invoices_Product Invoices_ProductGetByID(int InvoiceID, bool type)
        {
            Invoices_Product invoices_product = db.Invoices_Products.SingleOrDefault<Invoices_Product>
                (i => i.InvoiceID == InvoiceID && i.Type == type);
            return invoices_product;
        }

        public bool Invoices_ProductInsert(Invoices_Product InvoicePro)
        {
            try
            {
                db.Invoices_Products.InsertOnSubmit(InvoicePro);
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public bool Invoices_ProductDeleteByID(int InvoiceID, bool type)
        {
            Invoices_Product invoices_product = db.Invoices_Products.SingleOrDefault<Invoices_Product>
                (i => i.InvoiceID == InvoiceID && i.Type == type);

            if (invoices_product == null)
                return false;
            db.Invoices_Products.DeleteOnSubmit(invoices_product);
            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public bool Invoices_ProductDeleteProduct(int InvoiceID, bool type,int ProID)
        {
            Invoices_Product invoices_product = db.Invoices_Products.SingleOrDefault<Invoices_Product>
                (i => i.InvoiceID == InvoiceID && (i.Type == type && i.ProductID == ProID));

            if (invoices_product == null)
                return false;
            db.Invoices_Products.DeleteOnSubmit(invoices_product);
            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public bool Invoices_ProductCheckDuplicateInvoiceID(int InvoiceID, bool type)
        {
            var invoicepro = from i in db.Invoices_Products
                             select i;

            if (invoicepro.ToList<Invoices_Product>().Count == 0)
                return true;
            else
            {
                Invoices_Product invoices_product = db.Invoices_Products.SingleOrDefault<Invoices_Product>
                (i => i.InvoiceID == InvoiceID && i.Type == type);

                if (invoices_product == null)
                    return true;
                return false;
            }
        }

        public bool Invoices_ProductUpdate(Invoices_Product InvoicePro)
        {
            Invoices_Product invoices_product = db.Invoices_Products.SingleOrDefault<Invoices_Product>
                (i => i.InvoiceID == InvoicePro.InvoiceID && (i.Type == InvoicePro.Type
                    && i.ProductID == InvoicePro.ProductID));

            if (invoices_product == null)
                return false;

            invoices_product.InvoiceID = InvoicePro.InvoiceID;
            invoices_product.ProductID = InvoicePro.ProductID;
            invoices_product.Price = InvoicePro.Price;
            invoices_product.Quantity = InvoicePro.Quantity;
            invoices_product.TotalValues = InvoicePro.TotalValues;
            invoices_product.Type = InvoicePro.Type;

            try
            {
                db.SubmitChanges();
                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex);
                return false;
            }
        }

        public ImportInvoice GetImportInvoiceByImportID(int importID)
        {
            ImportInvoice im = db.ImportInvoices.Single<ImportInvoice>(import => import.ImportID == importID);
            return im;
        }

        public void DeleteImportInvoiceByImportID(ImportInvoice Import)
        {
            ImportInvoice import = db.ImportInvoices.Single<ImportInvoice>(im => im.ImportID == Import.ImportID);
            db.ImportInvoices.DeleteOnSubmit(import);
            try
            {
                db.SubmitChanges();
            }
            catch (Exception ex)
            {
                throw new Exception(ex.ToString());

            }
        }

        public IEnumerable<ImportInvoice> GetImportInvoiceAdvanced(string invoiceName, string storeName, string supplierName, string employeeName)
        {
            string where = string.Empty;
            string connector = "AND";
            if (invoiceName != string.Empty)
                where += string.Format("{0} = {1} AND", Columns.InvoiceID, invoiceName);
            if (storeName != string.Empty)
                where += string.Format("{0}= LIKE '%{1}%' AND", Columns.StorageName, storeName);
            if (supplierName != string.Empty)
                where += string.Format("{0} LIKE '%{1}%' AND", Columns.SupplierName, supplierName);
            if (employeeName != string.Empty)
                where += string.Format("({0} LIKE '%{1}%' OR {2} LIKE '%{1}%')", Columns.LastName, employeeName, Columns.FirstName);
            if (where != string.Empty)
                where = "WHERE " + where.TrimEnd(connector.ToCharArray());
            return null;
        }

    }
}
